﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;

namespace NewRecordApp
{
    public partial class NewRecordViewForm : Form
    {
        private OleDbDataAdapter m_dbDA;
        private DataSet m_dsPayment;

        public NewRecordViewForm()
        {
            // Initialize data
            m_dsPayment = new DataSet();

            InitializeComponent();
            this.WindowState = FormWindowState.Maximized;
            this.dataGridView1.Dock = System.Windows.Forms.DockStyle.Fill;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

            String queryAll = "SELECT * FROM [Sheet1$]";
            String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
            //String xlsPath = m_file;

            String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath.ToString() +
                ";Extended Properties='Excel 8.0;IMEX=1';";
            try
            {
                m_dbDA = new OleDbDataAdapter(queryAll, strConn);
                DataSet dsPaidXls = new DataSet();
                m_dbDA.Fill(dsPaidXls,"[Sheet1$]");

                int tblNo = dsPaidXls.Tables.Count;
                String tblName = dsPaidXls.Tables[0].TableName;
                DataTable tbl =  dsPaidXls.Tables[tblName];

                foreach (DataRow row in tbl.Rows)
                {
                    Console.WriteLine("Content of Row {0}", tbl.Rows.IndexOf(row));
                    displayRow(row);
                }

                //Delete Empty Rows  >>>>
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = dsPaidXls.Copy();
                List<int> emptyRowIndex = new List<int>();
                int colIndex = 1;
              
                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    bool isRowsEmpty = false;
                    foreach (DataColumn col in tmpDataSet.Tables[0].Columns)
                    {
                        Console.WriteLine("ColumnName ({0}), ColumnValue ({1}) \r\n", 
                            col.ColumnName, row[col]);
                        if (row[col].ToString() != "")
                        {
                            isRowsEmpty = false;
                            break;
                        }
                        else
                            isRowsEmpty = true;
                    }
                    if (isRowsEmpty)
                    {
                        emptyRowIndex.Add(colIndex);
                        row.Delete();
                        Console.WriteLine("{0}", row.RowState);
                    }
                    colIndex++;
                }
                // End <<

                // Add two column >>> Add
                tmpDataSet.Tables[0].Columns.Add("Currency", typeof(string));
                tmpDataSet.Tables[0].Columns.Add("Instrument", typeof(string));
                tmpDataSet.Tables[0].Columns.Add("P_ID", typeof(int));
                tmpDataSet.Tables[0].AcceptChanges();


                //find out currency in first column
                int rowNumber = tmpDataSet.Tables[0].Rows.Count;
                String[] tmpCurrencyArray;
                String currencyName = "";
                String[] tmpInstrumentArray;
                String strInstrument = "";
                List<int> delRowIndex = new List<int>();

                for (int i = 0; i < rowNumber; i++)
                {
                    // Find out Currency  >>> 
                    if (tmpDataSet.Tables[0].Rows[i][0].ToString() != "")
                    {  
                        if (tmpDataSet.Tables[0].Rows[i][0].GetType() == typeof(string))
                        {
                            string strLogicalNumber = tmpDataSet.Tables[0].Rows[i][0].ToString();
                            Match matchCurrency = Regex.Match((string)strLogicalNumber, @"^Currency", RegexOptions.IgnoreCase);
                            Match matchInstrument = Regex.Match((string)strLogicalNumber, @"^Instrument", RegexOptions.IgnoreCase);
                            if (matchCurrency.Success)
                            {
                                tmpCurrencyArray = ((string)strLogicalNumber).Split(':');
                                currencyName = tmpCurrencyArray[1].Trim();
                                currencyName = currencyName.Replace(" ", "");
                                delRowIndex.Add(i);
                            }
                            else if (matchInstrument.Success)
                            {
                                tmpInstrumentArray = ((string)strLogicalNumber).Split(':');
                                strInstrument = tmpInstrumentArray[1].Trim();
                                strInstrument = strInstrument.Replace(" ", "");
                                delRowIndex.Add(i);
                            }
                        }
                    }

                    if (currencyName != "")
                    {
                        tmpDataSet.Tables[0].Rows[i]["Currency"] = currencyName;
                    }

                    if (strInstrument != "")
                    {
                        tmpDataSet.Tables[0].Rows[i]["Instrument"] = strInstrument;
                    }
                    // End <<<<
                    //Console.WriteLine(tmpDataSet.Tables[0].Rows[i][0].ToString());
                }

                for (int i = 0; i < delRowIndex.Count; i++ )
                {
                    int j = delRowIndex[i];
                    tmpDataSet.Tables[0].Rows[j].Delete();
                }

//                 foreach (DataRow row in tmpDataSet.Tables[0].Rows)
//                 {
//                     DateTime dt = DateTime.Parse(row["Maturity Date"].ToString());
//                     row["Maturity Date"] = dt.ToShortDateString();
//                 }
                tmpDataSet.Tables[0].AcceptChanges();
                m_dsPayment = tmpDataSet.Copy();
                m_dsPayment.Tables[0].TableName = "TPayment";
                dataGridView1.DataSource = m_dsPayment;
                dataGridView1.DataMember = "TPayment";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        static void displayRow(DataRow row)
        {
            foreach(DataColumn col in row.Table.Columns)
            {
                Console.WriteLine("  {0}: {1} : {2}", col.ColumnName, row[col], row[col].GetType());
            }
        }

        public DataSet getPaymentDataSet()
        {
            return m_dsPayment;
        }
    }       
}
